﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using SqlSugar;
using VOL.Core.DbSqlSugar;
using VOL.demo.IRepositories.ShopOrder;
using VOL.Entity.DomainModels;

namespace VOL.demo.Repositories.ShopOrder
{
    public class ShopOrderRepository : IShopOrderRepository
    {
        private readonly ISqlSugarClient _dbContext;

        public ShopOrderRepository()
        {
            _dbContext = DbManger.Db; // 使用默认的数据库连接
        }

        /// <summary>
        /// 获取SqlSugar客户端
        /// </summary>
        public ISqlSugarClient DbContext => _dbContext;

        /// <summary>
        /// 根据ID获取销售订单
        /// </summary>
        /// <param name="id">销售订单ID</param>
        /// <returns>销售订单信息</returns>
        public async Task<shopordermodel> GetByIdAsync(long id)
        {
            return await _dbContext.Queryable<shopordermodel>()
                .Where(x => x.Id == id && x.IsDelete == 0)
                .FirstAsync();
        }

        /// <summary>
        /// 获取所有销售订单
        /// </summary>
        /// <returns>销售订单列表</returns>
        public async Task<List<shopordermodel>> GetAllAsync()
        {
            return await _dbContext.Queryable<shopordermodel>()
                .Where(x => x.IsDelete == 0)
                .ToListAsync();
        }

        /// <summary>
        /// 分页查询销售订单
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="whereExpression">查询条件</param>
        /// <returns>分页结果</returns>
        public async Task<(List<shopordermodel> Data, int TotalCount)> GetPageListAsync(int pageIndex, int pageSize,
            Expression<Func<shopordermodel, bool>> whereExpression = null)
        {
            var query = _dbContext.Queryable<shopordermodel>()
                .Where(x => x.IsDelete == 0);

            if (whereExpression != null)
            {
                query = query.Where(whereExpression);
            }

            RefAsync<int> totalCount = 0;
            var data = await query
                .OrderByDescending(x => x.CreateDate)
                .ToPageListAsync(pageIndex, pageSize, totalCount);

            return (data, totalCount);
        }

        /// <summary>
        /// 分页查询销售订单联查结果（包含客户名称、部门名称、用户名称）
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="shopOrderCode">销售单号（可选）</param>
        /// <param name="shopOrderDate">销售日期（可选）</param>
        /// <returns>分页结果</returns>                             
        public async Task<(List<ShopOrderDto> Data, int TotalCount)> GetPageListWithRelationsAsync(
            int pageIndex,
            int pageSize,
            string shopOrderCode = null,
            DateTime? shopOrderDate = null)
        {
            var query = _dbContext.Queryable<shopordermodel>()
                .LeftJoin<customermodel>((so, c) => so.CustomerId == c.Id)
                .LeftJoin<Sys_Department>((so, c, d) => so.DeptId == d.DepartmentId.ToString())
                .LeftJoin<Sys_User>((so, c, d, u) => so.UserId == u.User_Id)
                .Where((so, c, d, u) => so.IsDelete == 0);

            // 条件查询
            if (!string.IsNullOrEmpty(shopOrderCode))
            {
                query = query.Where((so, c, d, u) => so.ShopOrderCode.Contains(shopOrderCode));
            }

            if (shopOrderDate.HasValue)
            {
                var startDate = shopOrderDate.Value.Date;
                var endDate = startDate.AddDays(1);
                query = query.Where((so, c, d, u) => so.ShopOrderDate >= startDate && so.ShopOrderDate < endDate);
            }

            RefAsync<int> totalCount = 0;
            var data = await query
                .OrderByDescending((so, c, d, u) => so.CreateDate)
                .Select((so, c, d, u) => new ShopOrderDto
                {
                    // 销售订单字段
                    Id = so.Id,
                    ShopOrderCode = so.ShopOrderCode,
                    ShopOrderDate = so.ShopOrderDate,
                    CustomerId = so.CustomerId,
                    Contact = so.Contact,
                    ContactPhone = so.ContactPhone,
                    DeptId = so.DeptId,
                    UserId = so.UserId,
                    Remark = so.Remark,
                    Status = so.Status,
                    ShopAllNum = so.ShopAllNum,
                    ShopAllMoney = so.ShopAllMoney,
                    ShopAllGoods = so.ShopAllGoods,
                    Creator = so.Creator,
                    CreateDate = (DateTime)so.CreateDate,
                    OutStatus =(int) so.OutStatus,

                    // 关联表字段
                    CustomerName = c.CustomerName,
                    DeptName = d.DepartmentName,
                    UserName = u.UserName
                })
                .ToPageListAsync(pageIndex, pageSize, totalCount);

            return (data, totalCount);
        }

        /// <summary>
        /// 更新销售订单
        /// </summary>
        /// <param name="entity">销售订单实体</param>
        /// <returns>更新结果</returns>
        public async Task<bool> UpdateAsync(shopordermodel entity)
        {
            entity.ModifyDate = DateTime.Now;

            var result = await _dbContext.Updateable(entity)
                .IgnoreColumns(ignoreAllNullColumns: true)
                .ExecuteCommandAsync();

            return result > 0;
        }

        /// <summary>
        /// 批量更新销售订单
        /// </summary>
        /// <param name="entities">销售订单实体列表</param>
        /// <returns>更新结果</returns>
        public async Task<bool> UpdateRangeAsync(List<shopordermodel> entities)
        {
            foreach (var entity in entities)
            {
                entity.ModifyDate = DateTime.Now;
            }

            var result = await _dbContext.Updateable(entities)
                .IgnoreColumns(ignoreAllNullColumns: true)
                .ExecuteCommandAsync();

            return result > 0;
        }

        /// <summary>
        /// 删除销售订单(软删除)
        /// </summary>
        /// <param name="id">销售订单ID</param>
        /// <returns>删除结果</returns>
        public async Task<bool> DeleteAsync(long id)
        {
            var entity = await GetByIdAsync(id);
            if (entity == null)
            {
                return false;
            }

            entity.IsDelete = 1;
            entity.ModifyDate = DateTime.Now;

            var result = await _dbContext.Updateable(entity)
                .UpdateColumns(x => new { x.IsDelete, x.ModifyDate })
                .ExecuteCommandAsync();

            return result > 0;
        }

        /// <summary>
        /// 批量删除销售订单(软删除)
        /// </summary>
        /// <param name="ids">销售订单ID列表</param>
        /// <returns>删除结果</returns>
        public async Task<bool> DeleteRangeAsync(long[] ids)
        {
            var result = await _dbContext.Updateable<shopordermodel>()
                .SetColumns(x => new shopordermodel
                {
                    IsDelete = 1,
                    ModifyDate = DateTime.Now
                })
                .Where(x => ids.Contains(x.Id))
                .ExecuteCommandAsync();

            return result > 0;
        }

        /// <summary>
        /// 根据条件查询销售订单
        /// </summary>
        /// <param name="whereExpression">查询条件</param>
        /// <returns>销售订单列表</returns>
        public async Task<List<shopordermodel>> QueryAsync(Expression<Func<shopordermodel, bool>> whereExpression)
        {
            return await _dbContext.Queryable<shopordermodel>()
                .Where(whereExpression)
                .Where(x => x.IsDelete == 0)
                .ToListAsync();
        }

        /// <summary>
        /// 执行SQL查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>查询结果</returns>
        public async Task<List<shopordermodel>> QueryBySqlAsync(string sql, object parameters = null)
        {
            return await _dbContext.Ado.SqlQueryAsync<shopordermodel>(sql, parameters);
        }

        /// <summary>
        /// 获取货品分页列表
        /// </summary>
        public async Task<(List<ProductDisplayDto> Data, int TotalCount)> GetProductPageListAsync(ProductSearchDto searchDto)
        {
            var query = _dbContext.Queryable<productinformationmodel>()
                .Where(p => p.IsDel == 0); // 使用正确的删除标识字段

            // 货品编号查询
            if (!string.IsNullOrEmpty(searchDto.GoodsCode))
            {
                query = query.Where(p => p.goodsCode.Contains(searchDto.GoodsCode));
            }

            // 货品名称查询
            if (!string.IsNullOrEmpty(searchDto.GoodsName))
            {
                query = query.Where(p => p.goodsName.Contains(searchDto.GoodsName));
            }

            // 获取总记录数
            var totalCount = await query.CountAsync();

            // 分页查询
            var data = await query
                .OrderBy(p => p.goodsCode)
                .Skip((searchDto.PageIndex - 1) * searchDto.PageSize)
                .Take(searchDto.PageSize)
                .Select(p => new ProductDisplayDto
                {
                    Id = p.Id,
                    GoodsCode = p.goodsCode,
                    GoodsName = p.goodsName,
                    SpecModel = p.specModel,
                    Status = p.status,
                    ProductTypeId = p.productTypeId,
                    SupplierId = p.supplierId,
                    PurchasePrice = p.purchasePrice,
                    SalePrice = p.salePrice,
                    CurrentInventory = p.currentInventory,
                    TotalInventoryAmount = p.totalInventoryamount,
                    Creator = p.Creator,
                    CreateDate = p.CreateDate
                })
                .ToListAsync();

            return (data, totalCount);
        }

        /// <summary>
        /// 获取部门级联列表
        /// </summary>
        public async Task<List<DepartmentCascadeDto>> GetDepartmentCascadeListAsync(DepartmentSearchDto searchDto)
        {
            var query = _dbContext.Queryable<Sys_Department>()
                .Where(d => d.Enable == 1); // 只查询启用的部门

            // 根据部门名称模糊查询
            if (!string.IsNullOrWhiteSpace(searchDto.DepartmentName))
            {
                query = query.Where(d => d.DepartmentName.Contains(searchDto.DepartmentName));
            }

            // 根据部门编号模糊查询
            if (!string.IsNullOrWhiteSpace(searchDto.DepartmentCode))
            {
                query = query.Where(d => d.DepartmentCode.Contains(searchDto.DepartmentCode));
            }

            // 如果指定了根部门ID，只查询该部门及其子部门
            if (searchDto.RootDepartmentId.HasValue)
            {
                // 这里需要递归查询所有子部门，先获取所有部门然后在内存中处理
                var allDepartments = await _dbContext.Queryable<Sys_Department>()
                    .Where(d => d.Enable == 1)
                    .ToListAsync();

                var targetDepartmentIds = GetAllChildDepartmentIds(allDepartments, searchDto.RootDepartmentId.Value);
                targetDepartmentIds.Add(searchDto.RootDepartmentId.Value);

                query = query.Where(d => targetDepartmentIds.Contains(d.DepartmentId));
            }

            var departments = await query
                .OrderBy(d => d.DepartmentCode)
                .Select(d => new DepartmentCascadeDto
                {
                    DepartmentId = d.DepartmentId,
                    DepartmentName = d.DepartmentName,
                    DepartmentCode = d.DepartmentCode,
                    ParentId = d.ParentId,
                    DepartmentType = d.DepartmentType,
                    Enable = d.Enable,
                    Remark = d.Remark
                })
                .ToListAsync();

            if (searchDto.IsTreeStructure)
            {
                // 构建树形结构
                return BuildDepartmentTree(departments);
            }
            else
            {
                // 返回平铺结构，但计算层级和路径
                return BuildFlatDepartmentList(departments);
            }
        }

        /// <summary>
        /// 构建部门树形结构
        /// </summary>
        private List<DepartmentCascadeDto> BuildDepartmentTree(List<DepartmentCascadeDto> departments)
        {
            var departmentDict = departments.ToDictionary(d => d.DepartmentId, d => d);
            var rootDepartments = new List<DepartmentCascadeDto>();

            foreach (var dept in departments)
            {
                if (dept.ParentId == null || !departmentDict.ContainsKey(dept.ParentId.Value))
                {
                    // 根部门
                    dept.Level = 0;
                    dept.DepartmentPath = dept.DepartmentName;
                    rootDepartments.Add(dept);
                }
                else
                {
                    // 子部门
                    var parent = departmentDict[dept.ParentId.Value];
                    dept.Level = parent.Level + 1;
                    dept.DepartmentPath = $"{parent.DepartmentPath} > {dept.DepartmentName}";
                    parent.Children.Add(dept);
                }
            }

            return rootDepartments;
        }

        /// <summary>
        /// 构建部门平铺列表（带层级信息）
        /// </summary>
        private List<DepartmentCascadeDto> BuildFlatDepartmentList(List<DepartmentCascadeDto> departments)
        {
            var departmentDict = departments.ToDictionary(d => d.DepartmentId, d => d);
            var result = new List<DepartmentCascadeDto>();

            // 先构建树形结构来计算层级
            var treeStructure = BuildDepartmentTree(departments);

            // 然后平铺展开
            FlattenDepartmentTree(treeStructure, result);

            return result;
        }

        /// <summary>
        /// 递归展开部门树为平铺列表
        /// </summary>
        private void FlattenDepartmentTree(List<DepartmentCascadeDto> tree, List<DepartmentCascadeDto> result)
        {
            foreach (var dept in tree)
            {
                result.Add(dept);
                if (dept.Children.Any())
                {
                    FlattenDepartmentTree(dept.Children, result);
                }
            }
        }

        /// <summary>
        /// 获取指定部门的所有子部门ID
        /// </summary>
        private List<Guid> GetAllChildDepartmentIds(List<Sys_Department> allDepartments, Guid parentId)
        {
            var result = new List<Guid>();
            var children = allDepartments.Where(d => d.ParentId == parentId).ToList();

            foreach (var child in children)
            {
                result.Add(child.DepartmentId);
                result.AddRange(GetAllChildDepartmentIds(allDepartments, child.DepartmentId));
            }

            return result;
        }

        /// <summary>
        /// 获取用户下拉选择列表
        /// </summary>
        public async Task<(List<UserSelectDto> Data, int TotalCount)> GetUserSelectListAsync(UserSearchDto searchDto)
        {
            var query = _dbContext.Queryable<Sys_User>()
                .Where(u => u.Enable == 1); // 只查询启用的用户

            // 根据用户名称模糊查询（包括账号和真实姓名）
            if (!string.IsNullOrWhiteSpace(searchDto.UserName))
            {
                query = query.Where(u => u.UserName.Contains(searchDto.UserName) || u.UserTrueName.Contains(searchDto.UserName));
            }

            // 根据角色ID精确查询
            if (searchDto.RoleId.HasValue)
            {
                query = query.Where(u => u.Role_Id == searchDto.RoleId.Value);
            }

            // 根据部门ID查询（需要在DeptIds字段中包含指定部门ID）
            if (searchDto.DepartmentId.HasValue)
            {
                var deptIdStr = searchDto.DepartmentId.Value.ToString();
                query = query.Where(u => u.DeptIds.Contains(deptIdStr));
            }

            RefAsync<int> totalCount = 0;
            var users = await query
                .OrderBy(u => u.UserTrueName)
                .Select(u => new UserSelectDto
                {
                    User_Id = u.User_Id,
                    UserName = u.UserName,
                })
                .ToPageListAsync(searchDto.PageIndex - 1, searchDto.PageSize, totalCount);

            return (users, totalCount);
        }

        /// <summary>
        /// 使用事务添加销售订单和销售订单明细
        /// </summary>
        /// <param name="createDto">销售订单和销售订单明细创建DTO</param>
        /// <returns>添加结果</returns>
        public async Task<bool> AddWithDetailsAsync(ShopOrderWithDetailsCreateDto createDto)
        {
            try
            {
                Console.WriteLine("=== Repository层开始处理销售订单添加（事务版本） ===");
                
                var result = await _dbContext.Ado.UseTranAsync(async () =>
                {
                    Console.WriteLine("开始事务处理");
                    
                    // 1. 创建销售订单实体并设置完整的系统字段
                    var shopOrder = new shopordermodel
                    {
                        // 业务字段
                        ShopOrderCode = createDto.ShopOrder.ShopOrderCode,
                        ShopOrderDate = createDto.ShopOrder.ShopOrderDate,
                        CustomerId = createDto.ShopOrder.CustomerId,
                        Contact = createDto.ShopOrder.Contact ?? "",
                        ContactPhone = createDto.ShopOrder.ContactPhone ?? "",
                        DeptId = createDto.ShopOrder.DeptId,
                        UserId = createDto.ShopOrder.UserId,
                        Status = createDto.ShopOrder.Status ?? 1, // 默认为草稿状态
                        Auditor = createDto.ShopOrder.AuditUser, // 使用正确的字段名
                        AuditTime = createDto.ShopOrder.AuditTime,
                        Remark = createDto.ShopOrder.Remark ?? "",
                        ShopAllNum = createDto.ShopOrder.ShopAllNum ?? 0,
                        ShopAllMoney = createDto.ShopOrder.ShopAllMoney ?? 0,
                        ShopAllGoods = createDto.ShopOrder.ShopAllGoods,
                            
                        // 系统字段 - 后端自动生成
                        CreateDate = DateTime.Now,
                        IsDelete = 0,
                        Creator = "system", // 这里可以从当前用户上下文获取
                        CreateID = 1, // 这里可以从当前用户上下文获取
                        InOutTypeId=1,
                        OutStatus = 1
                    };

                    Console.WriteLine($"准备插入销售订单: {shopOrder.ShopOrderCode}");
                    
                    // 2. 插入销售订单并获取自增ID
                    var shopOrderId = await _dbContext.Insertable(shopOrder)
                        .ExecuteReturnBigIdentityAsync();

                    Console.WriteLine($"销售订单插入成功，ID: {shopOrderId}");

                    // 3. 创建销售订单明细实体列表并设置完整的系统字段
                    var shopOrderDetailsList = new List<shoporderdetailsmodel>();
                    foreach (var detailDto in createDto.ShopOrderDetails)
                    {
                        var detail = new shoporderdetailsmodel
                        {
                            // 关联字段
                            ShopOrderId = shopOrderId,
                            
                            // 业务字段
                            GoodsId = detailDto.GoodsId,
                            ShopOrderNum = detailDto.ShopOrderNum,
                            ShopOrderMoney = detailDto.ShopOrderMoney,
                            ShopOrderAllMoney = detailDto.ShopOrderAllMoney,
                            Remark = detailDto.Remark ?? "",

                            // 系统字段 - 后端自动生成
                            CreateDate = DateTime.Now,
                            IsDelete = 0,
                            Creator = "system", // 这里可以从当前用户上下文获取
                            CreateID = 1, // 这里可以从当前用户上下文获取
                        };
                        
                        shopOrderDetailsList.Add(detail);
                    }

                    Console.WriteLine($"准备插入 {shopOrderDetailsList.Count} 条销售订单明细");

                    // 4. 批量插入销售订单明细
                    var detailsResult = await _dbContext.Insertable(shopOrderDetailsList)
                        .ExecuteCommandAsync();

                    Console.WriteLine($"销售订单明细插入成功，影响行数: {detailsResult}");

                    if (detailsResult != shopOrderDetailsList.Count)
                    {
                        throw new Exception($"销售订单明细插入失败，期望插入 {shopOrderDetailsList.Count} 条，实际插入 {detailsResult} 条");
                    }

                    Console.WriteLine("✅ 事务处理完成");
                    return true;
                });

                Console.WriteLine($"=== Repository层处理完成，结果: {result.IsSuccess} ===");
                return result.IsSuccess;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ Repository层异常: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw; // 重新抛出异常，让上层处理
            }
        }

        /// <summary>
        /// 获取所有客户简单列表（仅ID和名称）
        /// </summary>
        public async Task<List<CustomerSimpleDto>> GetAllCustomersSimpleAsync()
        {
            var customers = await _dbContext.Queryable<customermodel>()
                .Where(c => c.IsDel == 0 || c.IsDel == null) // 只查询未删除的客户
                .OrderBy(c => c.CustomerName)
                .Select(c => new CustomerSimpleDto
                {
                    Id = c.Id,
                    CustomerName = c.CustomerName
                })
                .ToListAsync();

            return customers;
        }
    }
}